﻿using Entity;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;
using CommandService;
using Tools;
using System.Diagnostics.Metrics;
using SysFramework;

namespace BLL
{
    public class SummaryBLL:SysFramework.PubMethod
    {
        Commands cmd;
        ReturnResult rsl;
        PlanBLL planBLL;
        public SummaryBLL()
        {
            cmd = new Commands();
            planBLL= new PlanBLL();
        }
        /// <summary>
        /// 生产计划进度
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageCount"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public ReturnResult PlanRateList(string pageIndex, string pageSize, ref string pageCount, string condition)
        {
            rsl = new ReturnResult();
            try
            {
                StringBuilder sb = new StringBuilder();
                string fileds = "plCode,proCode,proName,spec,cuName,cuCode,createtime,erpOrder,finishDate,number";
                sb.Append("isdel=0 ");

                if (!string.IsNullOrWhiteSpace(condition))
                {
                    var json = JsonConvert.DeserializeObject<JObject>(condition);
                    foreach (JProperty obj in json.Properties())
                    {
                        if (!string.IsNullOrWhiteSpace(obj.Value.ToString()))
                        {
                            sb.Append(" and " + obj.Name + " like '%" + obj.Value.ToString() + "%' ");
                      

                        }
                    }
                }
                var pTable = cmd.PageTable("V_PI_ProductPlan_info", pageIndex, pageSize, sb.ToString(), "id desc", ref pageCount, fileds);
                sb.Clear();
                sb.Append("select * from fun_CommonData_Info where ctype='gx' and status='Y' ");
                sb.Append(" and isdel=0");
                var gxTable = cmd.GetDataTable(sb.ToString());//全部生产工序

                #region 添加工序字段：工序完成标识、工序完成数
                string sStr = "Status", tStr = "Total",fStr="Waste";
                foreach (DataRow r in gxTable.Rows)
                {
                    if (!pTable.Columns.Contains(r["cCode"].ToString()))
                    {
                        pTable.Columns.Add(r["cCode"].ToString().ToLower()+ sStr);
                        pTable.Columns.Add(r["cCode"].ToString().ToLower()+ tStr);
                        pTable.Columns.Add(r["cCode"].ToString().ToLower() + fStr);
                    }
                }
                #endregion

                foreach (DataRow r in pTable.Rows)
                {
                    r.BeginEdit();
                    #region 计划工序进度
                    sb.Clear();
                    sb.Append("select isFinish,gxCode from pi_ProductionPlan_gx where plCode='"+ r["plCode"].ToString() +"' and isdel=0");
                    var pgxTable=cmd.GetDataTable(sb.ToString());
                    foreach (DataRow gr in pgxTable.Rows)
                    {
                        r[gr["gxCode"].ToString().ToLower()+ sStr] = gr["isFinish"].ToString();
                    }
                    #endregion

                    #region 完工数
                    foreach (DataRow gr in gxTable.Rows)
                    {
                        string gxCode = gr["cCode"].ToString();

                        sb.Clear();
                        sb.Append("select sum(CONVERT(decimal(18,2),number)) from bar_ProductBarCode_info where barStatus!='bar-drop' ");
                        sb.Append("and isInitial='N' and planCode='"+ r["plCode"].ToString() +"' and gxCode='"+ gxCode +"'");
                        var barTable=cmd.GetDataTable(sb.ToString());
                        r[gxCode.ToLower() + tStr] = barTable.Rows[0][0].ToString();
                        #region 废 料
                        sb.Clear();
                        sb.Append("select isnull(sum(convert(decimal(18,2),number)),'0') number from pi_PlanWaste_info ");
                        sb.Append("where plCode='"+ r["plCode"].ToString() +"' and gxCode='"+ gxCode +"' and isdel=0");
                        var wTable=cmd.GetDataTable(sb.ToString());
                        r[gxCode.ToLower() + fStr] = wTable.Rows[0][0].ToString();
                        #endregion
                    }
                    #endregion
                    r.EndEdit();
                }
                rsl.Data =cmd.TableToDictionary(pTable);
                rsl.Result = true;
            }
            catch (Exception ex)
            {
                rsl.Message = ex.Message;
            }
            return rsl;
        }
        /// <summary>
        /// 生产计划进度
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageCount"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public ReturnResult PlanCostList(string pageIndex, string pageSize, ref string pageCount, string condition)
        {
            rsl = new ReturnResult();
            try
            {
                StringBuilder sb = new StringBuilder();
                string fileds = "plCode,proCode,proName,spec,cuName,cuCode,createtime,erpOrder,finishDate,number";
                sb.Append("isdel=0 ");
                sb = this.CreateSelectCondition(sb,condition);
            
                var pTable = cmd.PageTable("V_PI_ProductPlan_info", pageIndex, pageSize, sb.ToString(), "id desc", ref pageCount, fileds);
                pTable.Columns.Add("cpTotal");//成品成本合计
                pTable.Columns.Add("gxTotal");//工序半成品成本合计
                pTable.Columns.Add("gxmTotal");//工序用料成本合计
                pTable.Columns.Add("bfTotal");//版费合计
                pTable.Columns.Add("wTotal");//废料成本合计
                pTable.Columns.Add("tranTotal");//运费成本合计
                pTable.Columns.Add("total");
                foreach (DataRow r in pTable.Rows)
                {
                    r.BeginEdit();

                    #region 成品、工序半成品(原辅料)成本  
                    sb.Clear();
                    //sb.Append("select cu_iUnitCost,gxCode,(CONVERT(decimal(18,2),b.number)* CONVERT(decimal(18,2),cu_iUnitCost)) total,");
                    sb.Append("select isnull(cu_iUnitCost,'0') cu_iUnitCost ,gxCode,(CONVERT(decimal(18,3),b.number)* CONVERT(decimal(18,3),isnull(cu_iUnitCost,'0'))) total,");
                    sb.Append(" barCode,b.number, b.proCode,p.proCode p_proCode from bar_ProductBarCode_info b ");
                    sb.Append(" inner join pi_ProductionPlan_info p on p.plCode=b.planCode");
                    sb.Append(" where barStatus !='bar-drop' and costPlan='" + r["plCode"].ToString() + "'");
                   var bcTable = cmd.GetDataTable(sb.ToString());
                    double yflValue = 0;//工序半成品(原辅料)成本
                    double cpValue = 0;//成品成本
                    foreach (DataRow br in bcTable.Rows)
                    {
                        if (br["proCode"].ToString() == br["p_proCode"].ToString())
                        {
                            cpValue += double.Parse(br["total"].ToString());
                        }
                        else
                        {
                            yflValue += double.Parse(br["total"].ToString());
                        }
                    }
                    r["cpTotal"] = Math.Round(cpValue, 3);
                    r["gxTotal"] = Math.Round(yflValue, 3);
                    #endregion

                    #region 工序用料成本
                    sb.Clear();
                    //sb.Append("select sum(convert(decimal(18,3),isnull(number,'0'))) number,isnull(uPrice,'0') uPrice from pi_PlanMaterial_list ");
                    //sb.Append(" where plCode='" + r["plCode"].ToString() + "' group by uPrice ");
                    sb.Append("select convert(decimal(18,3),isnull(number,'0')) number,isnull(uPrice,'0') uPrice,proCode from pi_PlanMaterial_list ");
                    sb.Append(" where plCode='" + r["plCode"].ToString() + "' ");
                    var tTable = cmd.GetDataTable(sb.ToString());
                    double gxmoney = 0;  //工序用料成本
                    double bgmoney = 0;   //版辊费用
                    var gxrows = tTable.Select(" proCode not like '%-%' ");
                    var bgrows = tTable.Select(" proCode like '%-%' ");
                    foreach (var s in gxrows)
                    {
                        gxmoney += double.Parse(s["number"].ToString()) * double.Parse(s["uPrice"].ToString());
                    }
                    gxmoney = Math.Round(gxmoney, 3);
                    foreach (var s in bgrows)
                    {
                        bgmoney += double.Parse(s["number"].ToString()) * double.Parse(s["uPrice"].ToString());
                    }
                    bgmoney = Math.Round(bgmoney, 3);
                    //foreach (DataRow gr in tTable.Rows)
                    //{
                    //    money += double.Parse(gr["number"].ToString()) * double.Parse(gr["uPrice"].ToString());
                    //}
                    //money= Math.Round(money, 3);
                    r["gxmTotal"] = gxmoney;
                    r["bfTotal"] = bgmoney;
                    #endregion

                    #region 废料成本
                    sb.Clear();
                    sb.Append("select (CONVERT(decimal(18,3),number)* CONVERT(decimal(18,3),uPrice)) total ");
                    sb.Append(" from pi_PlanWaste_info where isdel=0 and plCode='"+ r["plCode"].ToString() + "'");
                    tTable = cmd.GetDataTable(sb.ToString());
                    double wTotal = 0;
                    if (tTable.Rows.Count > 0)
                    {
                        wTotal = double.Parse(tTable.Rows[0][0].ToString());
                    }
                    r["wTotal"] = wTotal;
                    #endregion

                    sb.Clear();
                    sb.Append("select sum((CONVERT(decimal(18,3),yunfei) + CONVERT(decimal(18,3),songhuo))) total");
                    sb.Append(" from pi_Freight_sub s  inner join bar_ProductBarCode_info b on s.barCode=b.barCode ");
                    sb.Append(" and b.costPlan='"+ r["plCode"].ToString() + "'  where s.isdel=0 group by b.costPlan ");
                    tTable=cmd.GetDataTable(sb.ToString());
                    double transTotal = 0;
                    if (tTable.Rows.Count > 0)
                    {
                        transTotal =double.Parse(tTable.Rows[0][0].ToString());
                    }
                    r["tranTotal"]=transTotal;
                    r["total"] =  bgmoney + cpValue + transTotal; //transTotal + wTotal+ gxmoney + bgmoney + cpValue + yflValue   成品+版+运费
                    r.EndEdit();
                }
                rsl.Data = cmd.TableToDictionary(pTable);
                rsl.Result = true;
            }
            catch (Exception ex)
            {
                rsl.Message = ex.Message;
            }
            return rsl;
        }
        /// <summary>
        /// 计划成本明细
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageCount"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public ReturnResult PlanCostDetails(string pageIndex, string pageSize, ref string pageCount, string condition)
        {
            rsl = new ReturnResult();
            try
            {
                StringBuilder sb = new StringBuilder();
                var json = JsonConvert.DeserializeObject<JObject>(condition);
                var list = new List<Dictionary<string, string>>();
                if(json["dType"].ToString()== "cpDetail")
                {
                    //产成品成本
                    sb.Append(" barStatus !='bar-drop' and proCode in(select proCode from pi_ProductionPlan_info where ");
                    sb.Append(" plCode='" + json["plCode"].ToString() + "') and planCode='" + json["plCode"].ToString() + "' ");
                    list = cmd.PageList("V_BarCode_Info", pageIndex, pageSize, sb.ToString(), "id desc", ref pageCount, "");
                }
                else if (json["dType"].ToString() == "bcpDetail")
                {
                    //半成品成本
                    sb.Append(" barStatus !='bar-drop' and proCode not in(select proCode from pi_ProductionPlan_info where ");
                    sb.Append(" plCode='" + json["plCode"].ToString() + "') and planCode='" + json["plCode"].ToString() + "' ");
                    list = cmd.PageList("V_BarCode_Info", pageIndex, pageSize, sb.ToString(), "id desc", ref pageCount, "");
                }
                else if (json["dType"].ToString() == "pmDetail")
                {
                    //工序用料成本
                    var tableName = "V_pi_PlanMaterialList";
                    sb.Append(" plCode='" + json["plCode"].ToString() + "'");
                    if (json.Property("isBg") != null && json["isBg"].ToString() == "1")
                    {
                        tableName = "V_pi_PlanMaterialListBG";
                    }

                    list = cmd.PageList(tableName, pageIndex, pageSize, sb.ToString(), "id desc", ref pageCount, "");
                }
                else if (json["dType"].ToString() == "ysDetail")
                {
                    //运费成本
                    sb.Append(" costPlan='" + json["plCode"].ToString() + "' and isdel=0");
                    list = cmd.PageList("V_PI_FreightSub", pageIndex, pageSize, sb.ToString(), "id desc", ref pageCount, "");
                }
                else if (json["dType"].ToString() == "wsDetail")
                {
                    //废料成本
                    sb.Append(" plCode='" + json["plCode"].ToString() + "' and isdel=0");
                    list = cmd.PageList("V_PI_PlanWaste", pageIndex, pageSize, sb.ToString(), "id desc", ref pageCount, "");
                }
                rsl.Data = list;
                rsl.Result = true;
            }
            catch (Exception ex)
            {
                rsl.Message = ex.Message;
            }
            return rsl;
        }
        /// <summary>
        /// 计划统计
        /// </summary>
        /// <returns></returns>
        public ReturnResult PlanStatusTotal()
        {
            rsl=new ReturnResult();
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("select plCode,status,finishDate from pi_ProductionPlan_info where isdel=0");
                var pTable = cmd.GetDataTable(sb.ToString());
                Dictionary<string,string>  dic=new Dictionary<string, string>();
            
                var dr = pTable.Select("status='ps-doing'");
                dic["ingCount"]=dr.Length.ToString();
                dr= pTable.Select("status='ps-pg-set'");
                dic["pgCount"] = dr.Length.ToString();
                int dCount = 0;
                foreach (DataRow r in pTable.Rows)
                {
                    if (r["status"].ToString() != "ps-finish" && DateTime.Parse(r["finishDate"].ToString()).Date < DateTime.Now.Date)
                    {
                      dCount++;
                    }
                  
                }
                dic["yqCount"] = dCount.ToString();
                sb.Clear();
                sb.Append("select parValue from sys_parameters_info where parCode='work_warehouse_code'");
                var parTable=cmd.GetDataTable(sb.ToString());
                string cjkCode = "8";
                if (parTable.Rows.Count > 0 && !string.IsNullOrWhiteSpace(parTable.Rows[0]["parValue"].ToString()))
                {
                    cjkCode = parTable.Rows[0]["parValue"].ToString().Trim();
                }
                sb.Clear();
                sb.Append("select distinct l.barCode  from pi_ProductsOccupation_list l");
                sb.Append(" inner join bar_ProductBarCode_info b on b.barCode=l.barCode and b.wareHouse !='' and b.wareHouse !='"+ cjkCode + "'");
                sb.Append(" where l.barCode !='' and l.status='Y' and l.isdel=0 ");
                pTable = cmd.GetDataTable(sb.ToString());
                dic["zyCount"]=pTable.Rows.Count.ToString();

                sb.Clear();
                sb.Append("select count(barCode)  from bar_ProductBarCode_info where barStatus ='need-check' and status='Y'");
                pTable = cmd.GetDataTable(sb.ToString());
                dic["jyCount"] = pTable.Rows[0][0].ToString();

                sb.Clear();
                sb.Append("select count(barCode)  from bar_ProductBarCode_info where barStatus ='need-in' and status='Y' and CONVERT(decimal(18,2),number) !='0' ");
                pTable = cmd.GetDataTable(sb.ToString());
                dic["rkCount"] = pTable.Rows[0][0].ToString();


                rsl.Data = dic;
                rsl.Result= true;
            }
            catch (Exception ex)
            {
               rsl.Message = ex.Message;
            }
            return rsl;
        }
        /// <summary>
        /// 计划成本明细
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageCount"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public ReturnResult GetBarStockList(string pageIndex, string pageSize, string condition,string baseCode, ref string pageCount)
        {
            rsl = new ReturnResult();
            try
            {
                //var json = JsonConvert.DeserializeObject<JObject>(condition);
                StringBuilder sb = new StringBuilder();
                sb.Append("1=1 ");
                //sb = CreateSelectCondition(sb,condition);
                if (!string.IsNullOrWhiteSpace(condition))
                {
                    var json = JsonConvert.DeserializeObject<JObject>(condition);
                    foreach (JProperty obj in json.Properties())
                    {
                        if (!string.IsNullOrWhiteSpace(obj.Value.ToString()))
                        {
                            if (!string.IsNullOrWhiteSpace(sb.ToString()))
                            {
                                sb.Append(" and ");
                            }
                            if (obj.Name == "wareHouse")
                            {
                                sb.Append("wareHouse='" + obj.Value.ToString() + "' ");
                            }
                            else
                            {
                                sb.Append(obj.Name + " like '%" + obj.Value.ToString() + "%' ");
                            }
                        }
                    }
                }
                var list = cmd.PageList("V_Bar_BarStockInfo", pageIndex, pageSize, sb.ToString(), "number desc", ref pageCount, "");
                if (AppSettingHelper.ReadAppSettings("ConntectERP") == "1")
                {
                    for (int i = 0; i < list.Count; i++)
                    {
                        string proCode = list[i]["proCode"].ToString();
                        string wareCode = list[i]["wareHouse"].ToString();
                        var erpTable=planBLL.ErpWhStockNumber(proCode, wareCode, baseCode);
                        if (erpTable.Rows.Count > 0)
                        {
                            list[i]["erpStock"] = erpTable.Rows[0]["number"].ToString();
                        }
                        
                    }
                }
                rsl.Data = list;
                rsl.Result = true;
            }
            catch (Exception ex)
            {
                rsl.Message = ex.Message;
            }
            return rsl;
        }
 
        /// <summary>
        /// 在库条码信息
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageCount"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public ReturnResult GetInHouseBarInfo(string pageIndex, string pageSize, ref string pageCount, string condition)
        {
            rsl = new ReturnResult();
            try
            {
                var json = JsonConvert.DeserializeObject<JObject>(condition);
                StringBuilder sb = new StringBuilder();
                sb.Append("proCode='"+ json["proCode"].ToString() +"' and wareHouse='"+ json["wareHouse"].ToString() +"' ");
                sb.Append(" and barStatus='need-out'");

                var list = cmd.PageList("V_BarCode_Info", pageIndex, pageSize, sb.ToString(), "id desc", ref pageCount, "");

                rsl.Data = list;
                rsl.Result = true;
            }
            catch (Exception ex)
            {
                rsl.Message = ex.Message;
            }
            return rsl;
        }
        /// <summary>
        /// 制袋机生产数据统计
        /// </summary>
        /// <param name="queStr"></param>
        /// <returns></returns>
        public ReturnResult GetGongXuTotalData(string queStr)
        {
            rsl = new ReturnResult();
            try
            {
                string sDate = getNowDate();
                string eDate=getNowDate(1);
                var json = JsonConvert.DeserializeObject<JObject>(queStr);
                StringBuilder sb = new StringBuilder();
                sb.Append("select proCode,proName,spec,machine,cd.cName machineName,planCode,sum(CONVERT(decimal(18,2),printNum)) number,'");
                sb.Append(string.Format("{0}至{1}",sDate,eDate)+"' hDate from V_BarCode_Info b left join fun_CommonData_info cd on cd.cCode=b.machine  ");
                sb.Append(" and cd.parentCode='scjt' where gxCode='" + json["gxCode"] +"' and barStatus not in('used-finish','bar-drop')");
                if (json.Property("sDate") != null && !string.IsNullOrWhiteSpace(json["sDate"].ToString()))
                {
                    sDate = json["sDate"].ToString();
                }
                if (json.Property("eDate") != null && !string.IsNullOrWhiteSpace(json["eDate"].ToString()))
                {
                    eDate = json["eDate"].ToString();
                }
                sb.Append(" and convert(date,happenDate)>='" + sDate + "' ");
                sb.Append(" and convert(date,happenDate)<='" + eDate + "' ");

                if (json.Property("machine") != null && !string.IsNullOrWhiteSpace(json["machine"].ToString()))
                {
                    sb.Append(" and  machine='" + json["machine"].ToString() + "' ");
                }
                if (json.Property("plCode") != null && !string.IsNullOrWhiteSpace(json["plCode"].ToString()))
                {
                    sb.Append(" and  b.planCode like'%" + json["plCode"].ToString() + "%' ");
                }
                sb.Append("group by proCode,proName,spec,machine,planCode,cd.cName");
                var table = cmd.GetDataTable(sb.ToString());
                rsl.Data =cmd.TableToDictionary(table);
                rsl.Result = true;
            }
            catch (Exception ex)
            {
                rsl.Message = ex.Message;
            }
            return rsl;
        }

        /// <summary>
        /// 条码追踪方法
        /// </summary>
        /// <param name="barCode"></param>
        /// <returns></returns>
        public ReturnResult BarInfoTrack(string dataStr)
        {
            rsl = new ReturnResult();
            try
            {
                var json = JsonConvert.DeserializeObject<JObject>(dataStr);
                if (string.IsNullOrWhiteSpace(json["barCode"].ToString()))
                {
                    rsl.Message = "查询条码不允许为空";return rsl;
                }
              
                DataTable table = new DataTable();
                this.TrackBar(json["barCode"].ToString(), "0", "0", ref table);
                rsl.Result=true;
                rsl.Data = cmd.TableToDictionary(table);
            }
            catch (Exception ex)
            {
                rsl.Message = ex.Message;
            }
            return rsl;
        }
        private void TrackBar(string barCode,string meter,string weight,ref DataTable table)
        {
            try
            {
                if ( table.Columns.Count < 1)
                {
                    table.Columns.Add("barCode");
                    table.Columns.Add("proCode");
                    table.Columns.Add("proName");
                    table.Columns.Add("proMeter");
                    table.Columns.Add("proWeight");
                    table.Columns.Add("createtime");
                    table.Columns.Add("createuser");
                    table.Columns.Add("gxName");
                    table.Columns.Add("pusersName"); 
                    table.Columns.Add("batchNo");
                    table.Columns.Add("useMeter");
                    table.Columns.Add("useWeight");
                    table.Columns.Add("reBars");
                }
                if (string.IsNullOrWhiteSpace(meter))
                {
                    meter = "0";
                }
                if (string.IsNullOrWhiteSpace(weight))
                {
                    weight = "0";
                }
                StringBuilder sb=new StringBuilder();
                sb.Append("select top 1 barCode,proCode,proName,proMeter,proWeight,createtime,createuser,gxName,pusersName,");
                sb.Append("boxCode batchNo,'"+meter+ "' useMeter,'"+ weight + "' useWeight,'' reBars ");
                sb.Append(" from V_BarCode_Info where barCode='" + barCode + "'");
                var barTable = cmd.GetDataTable(sb.ToString());
                if (barTable.Rows.Count > 0)
                {
                 
                    table.Rows.Add(barTable.Rows[0].ItemArray);
                }
                int rowIndex=table.Rows.Count;
                sb.Clear();
                sb.Append("select * from bar_BarPrintLogs where newBar='"+ barCode +"'");
                var logTable = cmd.GetDataTable(sb.ToString());
                string bars = "";             
                foreach (DataRow r in logTable.Rows)
                {
                 
                    string oldBar = r["oldBar"].ToString();
                    bars += string.Format("{0},", oldBar);
                    TrackBar(oldBar, r["meter"].ToString(), r["weight"].ToString(),ref table);
                }
                table.Rows[rowIndex - 1]["reBars"]=bars.TrimEnd(',');
            }
            catch (Exception ex)
            { 
               rsl.Message=ex.Message;
            }
        }
        public ReturnResult BarTrackTree(string datas)
        {
            rsl = new ReturnResult();
            try
            {
                var json = JsonConvert.DeserializeObject<JObject>(datas);
                if (string.IsNullOrWhiteSpace(json["barCode"].ToString()))
                {
                    rsl.Message = "请输入查询条码";
                    return rsl;
                }
                StringBuilder sb = new StringBuilder();
                List<string> tlist = new List<string>();
                loopAssembleRecords(json["barCode"].ToString(), ref tlist);
                if (tlist.Count > 0)
                {
                    List<Dictionary<string, object>> menuList = new List<Dictionary<string, object>>();
                    foreach (string bar in tlist)
                    {
                        Dictionary<string, object> fdic = new Dictionary<string, object>();
                        fdic["title"] = bar;
                        fdic["id"] = bar;
                        List<Dictionary<string, object>> cList = new List<Dictionary<string, object>>();
                        this.ChildUrl(bar, ref cList);
                        fdic["children"] = cList;
                        menuList.Add(fdic);
                    }
                    if (menuList.Count > 0)
                    {
                        rsl.Data = menuList;
                        rsl.Result = true;
                    }
                }
            }
            catch (Exception ex)
            {
                rsl.Message = ex.Message;
            }
            return rsl;
        }
        public ReturnResult ProductStock(string queStr,string baseCode)
        {
            rsl = new ReturnResult();
            try
            {
                OutBaseHelper outBase = new OutBaseHelper();
                var json = JsonConvert.DeserializeObject<JObject>(queStr);
                StringBuilder sb = new StringBuilder();
                sb.Append("select top 100 isnull(sum(CONVERT(decimal(18,2),iQuantity)),'0') number,s.cInvCode proCode,s.cWhCode wareCode,");
                sb.Append("w.cWhName wareName,p.cInvName proName,p.cInvStd spec,'0' useNum,'0' zyNum");
                sb.Append(" from CurrentStock s inner join Warehouse w on s.cWhCode=w.cWhCode ");
                sb.Append("inner join Inventory p on p.cInvCode=s.cInvCode where  1=1");
                if (json.Property("proCode") != null && !string.IsNullOrWhiteSpace(json["proCode"].ToString()))
                {
                    sb.Append(" and s.cInvCode='" + json["proCode"].ToString() + "'");
                }
                if (json.Property("proName") != null && !string.IsNullOrWhiteSpace(json["proName"].ToString()))
                {
                    sb.Append(" and p.cInvName='" + json["proName"].ToString() + "'");
                }
                if (json.Property("wareCode") != null && !string.IsNullOrWhiteSpace(json["wareCode"].ToString()))
                {
                    sb.Append(" and s.cWhCode='" + json["wareCode"].ToString() + "'");
                }
                sb.Append(" group by s.cInvCode,p.cInvName,p.cInvStd,s.cWhCode,w.cWhName");
                var table = outBase.getData(sb.ToString(), baseCode);
                foreach (DataRow r in table.Rows)
                {
                    sb.Clear();
                    sb.Append("select isnull(sum(convert(decimal(18,2),number)),'0') number from pi_ProductsOccupation_list where isdel=0 ");
                    sb.Append("and status='Y' and proCode='"+ r["proCode"].ToString() +"' and wareHouse='"+ r["wareCode"].ToString() +"'");
                    var tTable = cmd.GetDataTable(sb.ToString());
                    if (tTable.Rows.Count > 0)
                    {
                        r.BeginEdit();
                        r["zyNum"] = tTable.Rows[0]["number"].ToString();
                        r["useNum"] = (double.Parse(r["number"].ToString()) -double.Parse(tTable.Rows[0]["number"].ToString())).ToString();
                        r.EndEdit();
                    }
                }
                rsl.Result = true;
                rsl.Data = cmd.TableToDictionary(table);

            }
            catch (Exception ex)
            {
                rsl.Message = ex.Message;
            }
            return  rsl;
        }
        /// <summary>
        /// 查找顶级关联码
        /// </summary>
        /// <param name="barCode"></param>
        /// <param name="tlist"></param>
        public void loopAssembleRecords(string barCode, ref List<string> tlist)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" select  * from bar_BarPrintLogs where oldBar='" + barCode + "'");
            var table = cmd.GetDataTable(sb.ToString());
            if (table.Rows.Count < 1)
            {
                if (!tlist.Contains(barCode))
                {
                    tlist.Add(barCode);
                }
            }
            else
            {
                foreach (DataRow r in table.Rows)
                {
                    loopAssembleRecords(r["newBar"].ToString(), ref tlist);
                }

            }
        }
        private void ChildUrl(string barCode, ref List<Dictionary<string, object>> chlist)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" select  * from bar_BarPrintLogs where newBar='" + barCode + "'");
            var logTable = cmd.GetDataTable(sb.ToString());
            for (int x = 0; x < logTable.Rows.Count; x++)
            {
                List<Dictionary<string, object>> tempList = new List<Dictionary<string, object>>();
                var cdic = new Dictionary<string, object>();
                cdic["title"] =string.Format("{0}({1}/{2})", logTable.Rows[x]["oldBar"].ToString(), logTable.Rows[x]["useMeter"].ToString(), logTable.Rows[x]["useWeight"].ToString()) ;
                cdic["id"] = logTable.Rows[x]["oldBar"].ToString();
                ChildUrl(logTable.Rows[x]["oldBar"].ToString(), ref tempList);
                cdic["children"] = tempList;
                chlist.Add(cdic);
            }

        }
    }
}
